Exporting database to Excel table function developed and implemented by Android [with source code download]
- 2021-08-28 21:10:57
- OfStack
This article describes the Android development of the database to Excel table function. Share it for your reference, as follows:
Before 1 straight on the computer with Excel form to record the family bill, not long ago reinstall the system accidentally killed, sad for a long time, but I recorded the bill for 5 years! During this time, I used a notebook, which is easy to use but not in line with my habit, so I wrote a small bill record APP, and App was as small as only one Activity. Of course, I am developing more needs, hehe! You have now completed saving the daily recorded data to the Sqilte database, which can then be exported to the excel table. The code is also written with the help of some information on the Internet, and the code is relatively easy. Only one jxl. jar package is needed. Thank you for your help.
Attach the main code and then attach the file package:
MainActivity. java:
package com.ldm.familybill;
import java.io.File;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import android.annotation.SuppressLint;
import android.app.Activity;
import android.content.ContentValues;
import android.database.Cursor;
import android.os.Bundle;
import android.os.Environment;
import android.text.TextUtils;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.EditText;
import android.widget.Toast;
import com.ldm.db.DBHelper;
import com.ldm.excel.ExcelUtils;
@SuppressLint("SimpleDateFormat")
public class MainActivity extends Activity implements OnClickListener {
private EditText mFoodEdt;
private EditText mArticlesEdt;
private EditText mTrafficEdt;
private EditText mTravelEdt;
private EditText mClothesEdt;
private EditText mDoctorEdt;
private EditText mRenQingEdt;
private EditText mBabyEdt;
private EditText mLiveEdt;
private EditText mOtherEdt;
private EditText mRemarkEdt;
private Button mSaveBtn;
private File file;
private String[] title = { " Date ", " Food expenditure ", " Commodity item ", " Telephone bill for transportation ", " Travel ", " Dress expenditure ", " Medical care ", " Human feelings and visitors ", " Baby special ", " Rent water and electricity ", " Other expenses ", " Remarks " };
private String[] saveData;
private DBHelper mDbHelper;
private ArrayList<ArrayList<String>>bill2List;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
findViewsById();
mDbHelper = new DBHelper(this);
mDbHelper.open();
bill2List=new ArrayList<ArrayList<String>>();
}
private void findViewsById() {
mFoodEdt = (EditText) findViewById(R.id.family_bill_food_edt);
mArticlesEdt = (EditText) findViewById(R.id.family_bill_articles_edt);
mTrafficEdt = (EditText) findViewById(R.id.family_bill_traffic_edt);
mTravelEdt = (EditText) findViewById(R.id.family_bill_travel_edt);
mClothesEdt = (EditText) findViewById(R.id.family_bill_clothes_edt);
mDoctorEdt = (EditText) findViewById(R.id.family_bill_doctor_edt);
mRenQingEdt = (EditText) findViewById(R.id.family_bill_laiwang_edt);
mBabyEdt = (EditText) findViewById(R.id.family_bill_baby_edt);
mLiveEdt = (EditText) findViewById(R.id.family_bill_live_edt);
mOtherEdt = (EditText) findViewById(R.id.family_bill_other_edt);
mRemarkEdt = (EditText) findViewById(R.id.family_bill_remark_edt);
mSaveBtn = (Button) findViewById(R.id.family_bill_save);
mSaveBtn.setOnClickListener(this);
}
@Override
public void onClick(View v) {
if (v.getId() == R.id.family_bill_save) {
saveData = new String[] { new SimpleDateFormat("yyyy-MM-dd").format(new Date()), mFoodEdt.getText().toString().trim(), mArticlesEdt.getText().toString().trim(), mTrafficEdt.getText().toString().trim(), mTravelEdt.getText().toString().trim(), mClothesEdt.getText().toString().trim(), mDoctorEdt.getText().toString().trim(), mRenQingEdt.getText().toString().trim(), mBabyEdt.getText().toString().trim(), mLiveEdt.getText().toString().trim(), mOtherEdt.getText().toString().trim(), mRemarkEdt.getText().toString().trim() };
if (canSave(saveData)) {
ContentValues values = new ContentValues();
values.put("time", new SimpleDateFormat("yyyy-MM-dd").format(new Date()));
values.put("food", mFoodEdt.getText().toString());
values.put("use", mArticlesEdt.getText().toString());
values.put("traffic", mTrafficEdt.getText().toString());
values.put("travel", mTravelEdt.getText().toString());
values.put("clothes", mClothesEdt.getText().toString());
values.put("doctor", mDoctorEdt.getText().toString());
values.put("laiwang", mRenQingEdt.getText().toString());
values.put("baby", mBabyEdt.getText().toString());
values.put("live", mLiveEdt.getText().toString());
values.put("other", mOtherEdt.getText().toString());
values.put("remark", mRemarkEdt.getText().toString());
long insert = mDbHelper.insert("family_bill", values);
if (insert > 0) {
initData();
}
}
else {
Toast.makeText(this, " Please fill in any form 1 Item content ", Toast.LENGTH_SHORT).show();
}
}
}
@SuppressLint("SimpleDateFormat")
public void initData() {
file = new File(getSDPath() + "/Family");
makeDir(file);
ExcelUtils.initExcel(file.toString() + "/bill.xls", title);
ExcelUtils.writeObjListToExcel(getBillData(), getSDPath() + "/Family/bill.xls", this);
}
private ArrayList<ArrayList<String>> getBillData() {
Cursor mCrusor = mDbHelper.exeSql("select * from family_bill");
while (mCrusor.moveToNext()) {
ArrayList<String> beanList=new ArrayList<String>();
beanList.add(mCrusor.getString(1));
beanList.add(mCrusor.getString(2));
beanList.add(mCrusor.getString(3));
beanList.add(mCrusor.getString(4));
beanList.add(mCrusor.getString(5));
beanList.add(mCrusor.getString(6));
beanList.add(mCrusor.getString(7));
beanList.add(mCrusor.getString(8));
beanList.add(mCrusor.getString(9));
beanList.add(mCrusor.getString(10));
beanList.add(mCrusor.getString(11));
beanList.add(mCrusor.getString(12));
bill2List.add(beanList);
}
mCrusor.close();
return bill2List;
}
public static void makeDir(File dir) {
if (!dir.getParentFile().exists()) {
makeDir(dir.getParentFile());
}
dir.mkdir();
}
public String getSDPath() {
File sdDir = null;
boolean sdCardExist = Environment.getExternalStorageState().equals(android.os.Environment.MEDIA_MOUNTED);
if (sdCardExist) {
sdDir = Environment.getExternalStorageDirectory();
}
String dir = sdDir.toString();
return dir;
}
private boolean canSave(String[] data) {
boolean isOk = false;
for (int i = 0; i < data.length; i++) {
if (i > 0 && i < data.length) {
if (!TextUtils.isEmpty(data[i])) {
isOk = true;
}
}
}
return isOk;
}
}
CreateExcel. java:
package com.ldm.excel;
import java.io.File;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import android.os.Environment;
public class CreateExcel {
// Prepare settings excel Title of worksheet
private WritableSheet sheet;
/** Create Excel Workbook */
private WritableWorkbook wwb;
private String[] title = { " Date ", " Food expenditure ", " Commodity item ", " Telephone bill for transportation ", " Travel ", " Dress expenditure ", " Medical care ", " Human feelings and visitors ", " Baby special ", " Rent water and electricity ", " Other expenses ", " Remarks " };
public CreateExcel() {
excelCreate();
}
public void excelCreate() {
try {
/** Output excel The path of the file */
String filePath = Environment.getExternalStorageDirectory() + "/family_bill";
File file = new File(filePath, "bill.xls");
if (!file.exists()) {
file.createNewFile();
}
wwb = Workbook.createWorkbook(file);
/** Add Parts 1 Worksheet and set the 1 A Sheet The name of */
sheet = wwb.createSheet(" Household account sheet ", 0);
}
catch (Exception e) {
e.printStackTrace();
}
}
public void saveDataToExcel(int index, String[] content) throws Exception {
Label label;
for (int i = 0; i < title.length; i++) {
/**Label(x,y,z) Among them x Represents the number of the cell x+1 Column, column y+1 Row , The contents of the cell are y
* In Label Specifies the location and contents of cells in child objects of the
* */
label = new Label(i, 0, title[i]);
/** Add the defined cells to the worksheet */
sheet.addCell(label);
}
/*
* Fill data into cells
* Need to use jxl.write.Number
* The path must use its full path, or an error will occur
*/
for (int i = 0; i < title.length; i++) {
Label labeli = new Label(i, index, content[i]);
sheet.addCell(labeli);
}
// Write data
wwb.write();
// Close a file
wwb.close();
}
}
DBHelper. java:
package com.ldm.db;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
public class DBHelper extends SQLiteOpenHelper {
public static final String DB_NAME = "ldm_family"; // DB name
private Context mcontext;
private DBHelper mDbHelper;
private SQLiteDatabase db;
public DBHelper(Context context) {
super(context, DB_NAME, null, 11);
this.mcontext = context;
}
public DBHelper(Context context, String name, CursorFactory factory, int version) {
super(context, name, factory, version);
}
/**
* User number 1 The operation called when using the software for the first time, which is used to get the database creation statement ( SW ) , Then create the database
*/
@Override
public void onCreate(SQLiteDatabase db) {
String sql = "create table if not exists family_bill(id integer primary key,time text,food text,use text,traffic text,travel text,clothes text,doctor text,laiwang text,baby text,live text,other text,remark text)";
db.execSQL(sql);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
}
/* Open the database , Use if it is already open, otherwise create */
public DBHelper open() {
if (null == mDbHelper) {
mDbHelper = new DBHelper(mcontext);
}
db = mDbHelper.getWritableDatabase();
return this;
}
/* Close the database */
public void close() {
db.close();
mDbHelper.close();
}
/** Add data */
public long insert(String tableName, ContentValues values) {
return db.insert(tableName, null, values);
}
/** Query data */
public Cursor findList(String tableName, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy, String limit) {
return db.query(tableName, columns, selection, selectionArgs, groupBy, having, orderBy, limit);
}
public Cursor exeSql(String sql) {
return db.rawQuery(sql, null);
}
}
ExcelUtils. java:
package com.ldm.excel;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.List;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.write.Label;
import jxl.write.WritableCell;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import android.content.Context;
import android.widget.Toast;
public class ExcelUtils {
public static WritableFont arial14font = null;
public static WritableCellFormat arial14format = null;
public static WritableFont arial10font = null;
public static WritableCellFormat arial10format = null;
public static WritableFont arial12font = null;
public static WritableCellFormat arial12format = null;
public final static String UTF8_ENCODING = "UTF-8";
public final static String GBK_ENCODING = "GBK";
public static void format() {
try {
arial14font = new WritableFont(WritableFont.ARIAL, 14, WritableFont.BOLD);
arial14font.setColour(jxl.format.Colour.LIGHT_BLUE);
arial14format = new WritableCellFormat(arial14font);
arial14format.setAlignment(jxl.format.Alignment.CENTRE);
arial14format.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
arial14format.setBackground(jxl.format.Colour.VERY_LIGHT_YELLOW);
arial10font = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD);
arial10format = new WritableCellFormat(arial10font);
arial10format.setAlignment(jxl.format.Alignment.CENTRE);
arial10format.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
arial10format.setBackground(jxl.format.Colour.LIGHT_BLUE);
arial12font = new WritableFont(WritableFont.ARIAL, 12);
arial12format = new WritableCellFormat(arial12font);
arial12format.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
}
catch (WriteException e) {
e.printStackTrace();
}
}
public static void initExcel(String fileName, String[] colName) {
format();
WritableWorkbook workbook = null;
try {
File file = new File(fileName);
if (!file.exists()) {
file.createNewFile();
}
workbook = Workbook.createWorkbook(file);
WritableSheet sheet = workbook.createSheet(" Household account sheet ", 0);
sheet.addCell((WritableCell) new Label(0, 0, fileName, arial14format));
for (int col = 0; col < colName.length; col++) {
sheet.addCell(new Label(col, 0, colName[col], arial10format));
}
workbook.write();
}
catch (Exception e) {
e.printStackTrace();
}
finally {
if (workbook != null) {
try {
workbook.close();
}
catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
@SuppressWarnings("unchecked")
public static <T> void writeObjListToExcel(List<T> objList, String fileName, Context c) {
if (objList != null && objList.size() > 0) {
WritableWorkbook writebook = null;
InputStream in = null;
try {
WorkbookSettings setEncode = new WorkbookSettings();
setEncode.setEncoding(UTF8_ENCODING);
in = new FileInputStream(new File(fileName));
Workbook workbook = Workbook.getWorkbook(in);
writebook = Workbook.createWorkbook(new File(fileName), workbook);
WritableSheet sheet = writebook.getSheet(0);
for (int j = 0; j < objList.size(); j++) {
ArrayList<String> list=(ArrayList<String>) objList.get(j);
for (int i = 0; i < list.size(); i++) {
sheet.addCell(new Label(i, j+1, list.get(i), arial12format));
}
}
writebook.write();
Toast.makeText(c, " Save successfully ", Toast.LENGTH_SHORT).show();
}
catch (Exception e) {
e.printStackTrace();
}
finally {
if (writebook != null) {
try {
writebook.close();
}
catch (Exception e) {
e.printStackTrace();
}
}
if (in != null) {
try {
in.close();
}
catch (IOException e) {
e.printStackTrace();
}
}
}
}
}
public static Object getValueByRef(Class cls, String fieldName) {
Object value = null;
fieldName = fieldName.replaceFirst(fieldName.substring(0, 1), fieldName.substring(0, 1).toUpperCase());
String getMethodName = "get" + fieldName;
try {
Method method = cls.getMethod(getMethodName);
value = method.invoke(cls);
}
catch (Exception e) {
e.printStackTrace();
}
return value;
}
}
Attachment: Click here to download the complete source code.
For more readers interested in Android related contents, please check the topics on this site: "Summary of Android File Operation Skills", "Summary of View Skills in Android View", "Summary of activity Operation Skills in Android Programming", "Summary of Android Layout layout Skills", "Introduction and Advanced Tutorial of Android Development", "Summary of Android Resource Operation Skills" and "Summary of Android Control Usage"
I hope this article is helpful to everyone's Android programming.